"""
数据库修复脚本
用于修复users表中缺少password_history列的问题
"""

import sqlite3
import os

def fix_users_table():
    print("=== 用户表password_history列修复脚本 ===")
    
    # 确定项目根目录
    project_root = os.path.dirname(os.path.abspath(__file__))
    print(f"项目根目录: {project_root}")
    
    # 数据库文件路径 - 使用settings.py中配置的tickets.db
    db_path = os.path.join(project_root, 'tickets.db')
    print(f"数据库文件路径: {db_path}")
    
    # 检查数据库文件是否存在
    if os.path.exists(db_path):
        print("✓ 数据库文件存在")
    else:
        print("✗ 数据库文件不存在，将创建新的数据库文件")
        try:
            open(db_path, 'w').close()
            print("✓ 成功创建数据库文件")
        except Exception as e:
            print(f"✗ 创建数据库文件失败: {e}")
            return False
    
    try:
        # 连接到数据库
        print("连接到数据库...")
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # 检查users表是否存在
        print("检查users表是否存在...")
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='users'")
        table_exists = cursor.fetchone()
        
        if not table_exists:
            print("✗ users表不存在，创建新表...")
            create_sql = """
            CREATE TABLE users (
                id VARCHAR(36) PRIMARY KEY,
                username VARCHAR(50) UNIQUE NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                password_hash VARCHAR(255) NOT NULL,
                password_history TEXT,
                display_name VARCHAR(100),
                role VARCHAR(20) NOT NULL DEFAULT 'user',
                status VARCHAR(20) NOT NULL DEFAULT 'active',
                phone_number VARCHAR(255),
                avatar_url VARCHAR(255),
                department VARCHAR(255),
                last_login_at TIMESTAMP,
                created_at TIMESTAMP NOT NULL,
                updated_at TIMESTAMP NOT NULL,
                user_metadata TEXT
            )
            """
            cursor.execute(create_sql)
            print("✓ 成功创建users表")
        else:
            print("✓ users表存在，检查password_history列...")
            # 检查password_history列是否存在
            cursor.execute("PRAGMA table_info(users)")
            columns = cursor.fetchall()
            column_names = [col[1] for col in columns]
            
            print(f"当前列: {column_names}")
            
            if 'password_history' in column_names:
                print("✓ password_history列已存在，无需修复")
            else:
                print("✗ 缺少password_history列，正在添加...")
                try:
                    cursor.execute("ALTER TABLE users ADD COLUMN password_history TEXT")
                    print("✓ 成功添加password_history列")
                except Exception as e:
                    print(f"✗ 添加password_history列失败: {e}")
                    return False
        
        # 提交并关闭连接
        conn.commit()
        conn.close()
        print("✓ 数据库修复完成")
        return True
        
    except Exception as e:
        print(f"✗ 修复过程中出现错误: {e}")
        return False

if __name__ == "__main__":
    print("开始执行数据库修复...")
    success = fix_users_table()
    if success:
        print("\n🎉 数据库修复成功完成！")